package com.surfilter.massdata.spark.util;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.Date;
import java.util.List;
import java.util.Map;
import java.util.Properties;

import org.apache.commons.lang3.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;
import org.apache.spark.sql.Row;

import com.jolbox.bonecp.BoneCPConfig;
import com.jolbox.bonecp.BoneCPDataSource;

public class JdbcUtil {
	private static final Log log = LogFactory.getLog(JdbcUtil.class);
	private static final String configName = "config.properties";
	private static BoneCPDataSource boneCP = null;
	static {
		try {
			Properties props = new Properties();
			/*if(isWindows()){
				props.load(JdbcUtil.class.getResourceAsStream("/"+configName));
			}else{
				String jarPath=JarToolUtil.getJarDir()+File.separator;
				props.load(new FileInputStream(jarPath+configName));
			}*/
			props.load(JdbcUtil.class.getResourceAsStream("/"+configName));
			
			Class.forName(SysProps.getInstance().getValue("driverClass")).newInstance();
			BoneCPConfig config = new BoneCPConfig(props);
			boneCP = new BoneCPDataSource(config);
		} catch (Exception e) {
			log.error(e.getMessage(), e);
			throw new RuntimeException(e.getMessage(), e);
		}
	}
	
	/**
	 * 从JDBC中获得数据库连接
	 * 
	 * @return
	 */
	public static Connection getConnection() {
		Connection con = null;
		try {
			con = boneCP.getConnection();
		} catch (SQLException e) {
			if(log.isErrorEnabled()){
				log.error(e.getMessage(), e);
			}
			throw new RuntimeException(e.getMessage(), e);
		} 
		return con;
	}
	
	@Override
	protected void finalize() throws Throwable {
		boneCP.close();
		super.finalize();
	}

	/**
	 * 释放数据库连接
	 * 
	 * @param conn
	 */
	public static void close(Connection conn) {
		if (conn == null) {
			return;
		}
		try {
			if (conn.isClosed()) {
				return;
			}
			conn.close();
		} catch (SQLException e) {
			if(log.isErrorEnabled()){
				log.error(e.getMessage(), e);
			}
		}
	}

	/**
	 * 关闭STMT
	 * @param stmt
	 */
	public static void close(Statement stmt) {
		if(stmt == null){
			return;
		}
		try {
			stmt.close();
		} catch (SQLException e) {
			if(log.isErrorEnabled()){
				log.error(e.getMessage(), e);
			}
		}
	}

	/**
	 * 关闭RS
	 * 
	 * @param rs
	 */
	public static void close(ResultSet rs) {
		if(rs == null){
			return;
		}
		try {
			rs.close();
		} catch (SQLException e) {
			if(log.isErrorEnabled()){
				log.error(e.getMessage(), e);
			}
		}
	}

	/**
	 * 关闭STMT和CONN
	 * @param stmt
	 * @param conn
	 */
	public static void close(Statement stmt, Connection conn) {
		close(stmt);
		close(conn);
	}

	/**
	 * 关闭RS、STMT和CONN
	 * 
	 * @param rs
	 * @param stmt
	 * @param conn
	 */
	public static void close(ResultSet rs, Statement stmt, Connection conn) {
		close(rs);
		close(stmt);
		close(conn);
	}
	
	/**
	 * 判断当前操作系统是不是window
	 * 
	 * @return boolean
	 */
	public static boolean isWindows() {
		boolean flag = false;
		if (System.getProperties().getProperty("os.name").toUpperCase().indexOf("WINDOWS") != -1) {
			flag = true;
		}
		return flag;
	}
	
	
	public static void insertTable(Connection conn,List<Row> list,String table,String columns,String values) {
		
		CommonUtils.createSeqIfNotExists(table);
		String[] columnsSplit = columns.split(",");
		PreparedStatement ps = null;
		String sql = "insert into "+table+ "("+columns+") "+values;
		try {
			ps = conn.prepareStatement(sql);
			conn.setAutoCommit(false);
			int n = 0;
			for (Row row : list) {

				for (int i = 1; i < row.size()+1; i++) {
					ps.setObject(i, row.getAs(columnsSplit[i]));
				}
				ps.addBatch();

				if (++n % 5000 == 0) {
					ps.executeBatch();
					conn.commit();
				}
			}
			ps.executeBatch();
			conn.commit();
			ps.close();
		} catch (Exception e) {
			log.error("========="+table+"========");
			if(log.isErrorEnabled()){
				log.error(e.getMessage(),e);
			}
		}finally{
			
		}
	}
	
	public static void insertTable_oneline(Connection conn,Map map,String table,String columns,String values ) {
		CommonUtils.createSeqIfNotExists(table);
		String[] columnsSplit = columns.split(",");
		PreparedStatement ps = null;
		String sql = "insert into "+table+ "("+columns+") "+values;
		try {
			ps = conn.prepareStatement(sql);
			for (int i = 1; i < map.size()+1; i++) {
				ps.setObject(i, map.get(i));
			}
			ps.executeUpdate();
		    conn.commit();
			ps.close();
		} catch (Exception e) {
		   e.printStackTrace();
		}finally{
			
		}
	}
	
	public static  void deleteWeekTable(Date date,Connection conn,String table,String buss_type,String buss_value,int sta_range) throws SQLException {
		try{
			int year = DateUtil.getCurrentYear(date);
			int week = DateUtil.getCurrentWeek(date);
			StringBuffer queryCond = new StringBuffer();
			queryCond.append(" where year = "+year);
			queryCond.append("   and week = "+week);
			queryCond.append("   and buss_type = '"+buss_type+"'");
			if(StringUtils.isNotBlank(buss_value)){
				queryCond.append("   and buss_value = '"+buss_value+"'");
			}
//			else{
//				queryCond.append("   and buss_value is null");
//			}
			queryCond.append("   and sta_range = "+sta_range);
			StringBuffer sqlBuffer = new StringBuffer();
	        boolean flag = false;
	      //删除当前周昨日统计数据
        	sqlBuffer.append("delete from "+table+"");
        	sqlBuffer.append(queryCond);
        	flag = execSql(conn,sqlBuffer.toString());
		}catch(Exception e){
			e.printStackTrace();
		}finally{
		}
	
	}
	public static  void deleteMonthTable(Date date,Connection conn,String table,String buss_type,String buss_value,int sta_range) throws SQLException {
		try{
			int year = DateUtil.getCurrentYear(date);
			int month = DateUtil.getCurrentMonth(date);
			StringBuffer queryCond = new StringBuffer();
			queryCond.append(" where year = "+year);
			queryCond.append("   and month = "+month);
			queryCond.append("   and buss_type = '"+buss_type+"'");
			if(StringUtils.isNotBlank(buss_value)){
				queryCond.append("   and buss_value = '"+buss_value+"'");
			}
//			else{
//				queryCond.append("   and buss_value is null");
//			}
			queryCond.append("   and sta_range = "+sta_range);
			StringBuffer sqlBuffer = new StringBuffer();
	        boolean flag = false;
	      //删除当前月昨日统计数据
        	sqlBuffer.append("delete from "+table+"");
        	sqlBuffer.append(queryCond);
        	flag = execSql(conn,sqlBuffer.toString());
		}catch(Exception e){
			e.printStackTrace();
		}finally{
		}
	
	}
	public static  void deleteQuarterTable(Date date,Connection conn,String table,String buss_type,String buss_value,int sta_range) throws SQLException {
		try{
			int year = DateUtil.getCurrentYear(date);
			int quarter = DateUtil.getQuarter(date);
			StringBuffer queryCond = new StringBuffer();
			queryCond.append(" where year = "+year);
			queryCond.append("   and quarter = "+quarter);
			queryCond.append("   and buss_type = '"+buss_type+"'");
			if(StringUtils.isNotBlank(buss_value)){
				queryCond.append("   and buss_value = '"+buss_value+"'");
			}
//			else{
//				queryCond.append("   and buss_value is null");
//			}
			queryCond.append("   and sta_range = "+sta_range);
			StringBuffer sqlBuffer = new StringBuffer();
	        boolean flag = false;
	      //删除当前季昨日统计数据
        	sqlBuffer.append("delete from "+table+"");
        	sqlBuffer.append(queryCond);
        	flag = execSql(conn,sqlBuffer.toString());
		}catch(Exception e){
			e.printStackTrace();
		}finally{
		}
	
	}
	public static  void deleteYearTable(Date date,Connection conn,String table,String buss_type,String buss_value,int sta_range) throws SQLException {
		try{
			int year = DateUtil.getCurrentYear(date);
			StringBuffer queryCond = new StringBuffer();
			queryCond.append(" where year = "+year);
			queryCond.append("   and buss_type = '"+buss_type+"'");
			if(StringUtils.isNotBlank(buss_value)){
				queryCond.append("   and buss_value = '"+buss_value+"'");
			}
//			else{
//				queryCond.append("   and buss_value is null");
//			}
			queryCond.append("   and sta_range = "+sta_range);
			StringBuffer sqlBuffer = new StringBuffer();
	        boolean flag = false;
	      //删除当前年昨日统计数据
        	sqlBuffer.append("delete from "+table+"");
        	sqlBuffer.append(queryCond);
        	flag = execSql(conn,sqlBuffer.toString());
		}catch(Exception e){
			e.printStackTrace();
		}finally{
		}
	
	}
	public static boolean execSql(Connection conn,String sql){
    	boolean flag = true;
    	PreparedStatement ps = null;
    	try{
    		conn.setAutoCommit(false);
    		ps = conn.prepareStatement(sql);
          	ps.executeQuery();
            conn.commit();
          	flag = true;
          }catch(SQLException e){
         	 flag = false;
              try {
                  conn.rollback();
              } catch (SQLException e1) {
            	  e1.printStackTrace();
              }
          }finally{
        	  JdbcUtil.close(ps);
          }
    	return flag ;
    }
}

